package com.sq.partner.manager.dao.impl.credit;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Random;

import org.apache.log4j.Logger;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.springframework.stereotype.Repository;

import com.sq.partner.manager.dao.credit.CreditPlanDao;
import com.sq.partner.manager.dao.impl.BaseDaoImpl;
import com.sq.partner.manager.dao.impl.IGatewayAgentInfoMtDaoImpl;
import com.sq.partner.manager.mybatis.model.CreditPlan;
import com.sq.partner.manager.mybatis.model.NlifePlan;
import com.sq.partner.manager.mybatis.model.TradAgentInfo;
import com.sq.partner.manager.mybatis.model.TradAgentInfoRela;
import com.sq.partner.manager.mybatis.model.credit.credit10008;
import com.sq.partner.manager.util.DBUtil;
import com.sq.partner.manager.util.HttpConnectorUtil;
import com.sq.partner.manager.util.LogUtil;
import com.sq.partner.manager.util.Page;
import com.sq.partner.manager.util.SqlUtil;
import com.sq.partner.manager.util.StrUtil;
@Repository
public class CreditPlanDaoImpl extends BaseDaoImpl implements CreditPlanDao {
	private static Logger logger = LogUtil.getLog(CreditPlanDaoImpl.class);

	@Override
	public Page<Map<String, Object>> queryPageByCriteria(
			Page<Map<String, Object>> page) throws Exception {
		Map<String,Object> paramMap = page.getMap();
		
		int total = (Integer) this.query("queryCreditPlanCountByFilter", paramMap);
		page.setTotal(total);
		
		List<Map<String,Object>> resultList = this.queryList("queryCreditPlanPageByFilter", paramMap);
		page.setData(resultList);
		
		return page;
	}
	@Override
	public credit10008 queryCustBy(credit10008 credit10008,String href) throws Exception {
		SimpleDateFormat TRANSRDATE = new SimpleDateFormat("yyyy-MM-dd HH:MM:ss");
		String format = TRANSRDATE.format(new Date());
		StringBuffer xml = new StringBuffer();
		SimpleDateFormat sf = new SimpleDateFormat("yyyyMMddHHmmss");
		String SERIALNUMBER = sf.format(new Date());
		xml.append( "<?xml version=\"1.0\" encoding=\"UTF-8\"?>" + 
					 "<REQUEST>" + 
					 	"<Head>" + 
					 		"<TRANSRNO>"+10008+"</TRANSRNO>" + 
					 		"<PARTNERCODE>"+"XBHT"+"</PARTNERCODE>" + 
					 		"<PARTNERSUBCODE>"+"XBHT"+"</PARTNERSUBCODE>" + 
					 	"</Head>" + 
					 	"<MAIN>"+
					 		"<SERIALNUMBER>"+SERIALNUMBER+"</SERIALNUMBER>" + 
					 		"<TRANSRDATE>"+format+"</TRANSRDATE>" +
					 		"<CUST_TYPE>"+credit10008.getCustType()+"</CUST_TYPE>" +
					 	"</MAIN>" + 
					 	"<BODY>");
		if(credit10008.getCustType().equals("1")){
			xml.append("<IDENTIFY_NO>"+credit10008.getIdentifyNo()+"</IDENTIFY_NO>");
		}else{
			xml.append("<LICENSE_NO>"+(StrUtil.isNotEmpty(credit10008.getLicenseCode())?credit10008.getLicenseCode():"")+"</LICENSE_NO>" +
			 		   "<COMPANY_NAME>"+(StrUtil.isNotEmpty(credit10008.getCustomerName())?credit10008.getCustomerName():"")+"</COMPANY_NAME>" +
			 		   "<INSTITUTE_CODE>"+(StrUtil.isNotEmpty(credit10008.getInstituteCode())?credit10008.getInstituteCode():"")+"</INSTITUTE_CODE>");
		}
		xml.append( "</BODY>" + 
					"</REQUEST>");
		String message = xml.toString();
		if(href.equals("//10.196.20.30")){
			href = "http://10.196.20.148:6406/CreditService";
		}else if(href.equals("//10.196.21.30")){
			href = "http://10.196.21.31:6406/CreditService";
		}else if(href.equals("//10.196.22.25")){
			href = "http://10.196.22.30:6406/CreditService";
		}else if(href.equals("//localhost")){ 
			href = "http://10.196.20.148:6406/CreditService";
		}else{
			href="http://10.192.48.54:8039/CreditService";
		}
		String interaction = HttpConnectorUtil.interaction(href, message, "XBHT", "10008");
		Document document=null;
		document = DocumentHelper.parseText(interaction);
		Element root  = document.getRootElement();
		Element RETURN =null;
		String RETURNCODE = "";
		Element main =null;
		Element body = null;
	    RETURN= root.element("RETURN");  
	    main= root.element("MAIN"); 
	    RETURNCODE=main.elementTextTrim("RESULTCODE");
	    credit10008 reCredit = new credit10008();
	    if(RETURNCODE.equals("0000")){
	    	body = root.element("BODY");
	    	if(credit10008.getCustType().equals("1")){
	    		//客户代码   客户名称  性别  手机号码  民族  户籍所在地
	        	reCredit.setCustomerCode(body.elementTextTrim("CUSTOMERCODE"));
	        	reCredit.setCustomerName(body.elementTextTrim("CUSTOMERNAME"));
	        	reCredit.setSex(body.elementTextTrim("GENDER"));
	        	reCredit.setTelephone(body.elementTextTrim("TELEPHONE"));
	        	reCredit.setNation(body.elementTextTrim("NATION"));
	        	reCredit.setPesidence(body.elementTextTrim("RESIDENCE"));
	    	}else{
	    		reCredit.setCustomerCode(body.elementTextTrim("CUSTOMERCODE"));
	    		reCredit.setCustomerName(body.elementTextTrim("CUSTOMERNAME"));
	    		reCredit.setInstituteCode(body.elementTextTrim("ORGANIZATIONCODE"));
	    		reCredit.setLicenseCode(body.elementTextTrim("LICENSEEXPIRINGCODE"));
	    		reCredit.setMailingAddress(body.elementTextTrim("MAILINGADDRESS"));
	    	}
	    }
	    return reCredit;
	}
	
	@Override
	public boolean add(CreditPlan creditPlan) throws Exception {
		creditPlan.setInsert_time(new Date());
		creditPlan.setUpdate_time(new Date());
		return this.add("insertCreditPlan", creditPlan);
	}
	@Override
	public boolean update(CreditPlan creditPlan) throws Exception {
		creditPlan.setUpdate_time(new Date());
		return this.update("updateCreditPlan", creditPlan);
	}
	
	@Override
	public CreditPlan queryById(String plan_code) throws Exception {
		CreditPlan creditPlan = (CreditPlan) this.query("queryCreditPlanById", plan_code==null?"":plan_code);
		return creditPlan;
	}
	
	@Override
	public String queryNextPlanNo(String planStr) throws Exception {
		Object object = this.query("queryNextPlanNoByCredit", planStr);//参数为方案号的前缀
		return object==null?null:object.toString();
	}
	
	@Override
	public boolean delById(String plan_code) throws Exception {
		return this.delete("delCreditPlanById", plan_code==null?"":plan_code);
	}
	
	@Override
	public Page<Map<String, Object>> queryAgentInfoWithChannelPageByCriteria(
			Page<Map<String, Object>> page) throws Exception {
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			sqlbuf.append(" select t.id, t.channel_code, t.agent_type,t.C_BRKR_NME as INTERMEDIARY_NAME,t.MANAG_CODE,t.MANAG,")
				   .append("  sc.branch, sc.branch_name,T1.Code_Name,T2.CHANNEL_NAME,")
				   .append("  sc.zone, t.dpt_cde, t.service_code,")
				   .append("  t.emp_no, t.emp_name, t.team_code,t.team_name,")
				   .append("  d1.code_name AS channlecodedes, d2.code_name AS channletypedes, d3.code_name AS newbsnstypedes, t.agent_no, t.protocol_no,")
				   .append("  rela.insu_plan_no as planNO  from T_TRAD_AGENT_INFO t  ")
				   .append(" inner join T_GATEWAY_DICT T1 ON  t.AGENT_TYPE = T1.CODE  AND T1.CODE_TYPE = 'agent_type'")
				   .append(" LEFT JOIN T_GATEWAY_CHANNEL_INFO T2 ON t.CHANNEL_CODE = T2.CHANNEL_CODE AND T2.STATUS = '1'")
				   .append(" left join T_GATEWAY_SUB_CHANNEL sc on t.sub_channel_code =sc.sub_channel_code")
				   .append(" left join T_GATEWAY_DICT d1 on d1.code_type ='belong_to_channel' and t.channle_code = d1.code ")
			       .append(" left join T_GATEWAY_DICT d2 on d2.code_type ='channel_type' and t.channle_type = d2.code ")
				   .append(" left join T_GATEWAY_DICT d3 on d3.code_type ='new_business_source' and t.new_bsns_type = d3.code ")
				   .append(" left join T_TRAD_AGENT_INFO_RELA rela  on t.id=rela.agent_info_id   and  rela.insu_plan_no='"+page.getMap().get("planNo")+"'")
				   .append(" WHERE 1=1 ");

			String sql;
			List<String> list=new ArrayList<String>();
			List<Boolean> islike=new ArrayList<Boolean>();
			Map<String,Object> paramMap = page.getMap();
			
			String channelCode =StrUtil.getVal(paramMap.get("partnerNo"));
			if(StrUtil.isNotEmpty(channelCode)){
				sqlbuf.append(" and t.channel_code = ?");
				list.add(channelCode);
				islike.add(false);
				sqlbuf.append("  and SC.fk_channel_code=?");
				list.add(channelCode);
				islike.add(false);
			}
			String agentType =StrUtil.getVal(paramMap.get("agentType"));
			if(StrUtil.isNotEmpty(agentType)){
				sqlbuf.append(" and t.agent_type= ?");
				list.add(agentType);
				islike.add(false);
			}
			String dept_code = StrUtil.getVal(paramMap.get("dept_code"));
			if(StrUtil.isNotEmpty(dept_code) && !"00".equals(dept_code)){
			  sqlbuf.append(" and t.dpt_cde like ?");
			  list.add(dept_code+"%");
			  islike.add(false);
			}else if(StrUtil.isNotEmpty(dept_code) && "00".equals(dept_code)){
				sqlbuf.append(" and t.dpt_cde in (select dept_code from t_core_dept t connect by prior dept_code = snr_dept start with dept_code in ( ? ))");
				list.add(dept_code);
				islike.add(false);
			}
			sqlbuf.append("ORDER BY planNO ");
		/*	if(StrUtil.isNotEmpty(pa))*/
			sql=sqlbuf.toString();
			
			String confStatus = StrUtil.getVal(paramMap.get("confStatus"));
			if("Y".equals(confStatus)){
				sql ="SELECT * FROM ("+sql+") A WHERE  exists (SELECT 1 FROM T_TRAD_AGENT_INFO_RELA R  where R.Insu_Plan_No=? and R.AGENT_INFO_ID= A.ID )";
				String planNo = StrUtil.getVal(paramMap.get("planNo"));
				if(StrUtil.isNotEmpty(planNo)){
					list.add(planNo);
					islike.add(false);
				}
			}else if("N".equals(confStatus)){
				sql ="SELECT * FROM ("+sql+") A WHERE not exists (SELECT 1 FROM T_TRAD_AGENT_INFO_RELA R  where R.Insu_Plan_No=? and R.AGENT_INFO_ID= A.ID )";
				String planNo = StrUtil.getVal(paramMap.get("planNo"));
				if(StrUtil.isNotEmpty(planNo)){
					list.add(planNo);
					islike.add(false);
				}
			}else{
				sql = " SELECT * FROM ("+sql+") A WHERE 1=1 ";
			}
			
			String branch =StrUtil.getVal(paramMap.get("branch"));
			if(StrUtil.isNotEmpty(branch)){
				sql = sql +" AND A.branch like ?";
				list.add(branch);
				islike.add(true);
			}
			String branchName =StrUtil.getVal(paramMap.get("branchName"));
			if(StrUtil.isNotEmpty(branchName)){
				sql = sql +" AND A.branch_name like ?";
				list.add(branchName);
				islike.add(true);
			}
		/**/
			logger.debug("执行SQL：" + sql);
			
			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt.setObject(i+1, list.get(i));
				}
				
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);
			page.setTotal(total);
			//查出分页
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt1.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt1.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt1.executeQuery();
			List resultList = DBUtil.transRs2List(rs);
			page.setData(resultList);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		
		return page;
	}
	
	@Override
	public boolean queryByProdId(String prod_code) throws Exception {
		int n=(Integer) this.query("queryisExistPlanOrPartnerInfoByProdCodeByCredit",prod_code);
		if(n>0){
			return true;
		}
		return false;
	}
	
	@Override
	public List<CreditPlan> impCreditPlan(Map<String, Object> map)
			throws Exception {
		return this.queryList("impCreditPlanByMap", map);
	}
}
